﻿using Se.Log;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Se.Extend;
using Se.Common;

namespace Se.Data.Sql
{
    /// <summary>
    /// SQL Server 数据提供者
    /// </summary>
    public class SqlDataProvider : DBProviderBase
    {
        public SqlDataProvider(string strConnection)
            : base(strConnection)
        {
            this.SetConnection();
        }

        /// <summary>
        /// SQL Connection
        /// </summary>
        private SqlConnection _connection;

        public void SetConnection()
        {
            this._connection = new SqlConnection(this.ConnectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        public override void CheckConnect()
        {
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            {
                con.Open();
            }
        }

        /// <summary>
        /// 执行并获取Reader
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override IDataReader ExecuteReader(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            var reader = SqlHelper.ExecuteReader(this._connection, cmdType, cmdText, ConvertParams<SqlParameter>(parameters));
            return reader;
        }

        /// <summary>
        /// 执行sql(返回DataSet)
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            var dataSet = SqlHelper.ExecuteDataset(this._connection, cmdType, cmdText, ConvertParams<SqlParameter>(parameters));
            return dataSet;
        }

        /// <summary>
        /// 返回查询结果的第一行第一列
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override object ExecuteScalar(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            var val = SqlHelper.ExecuteScalar(this._connection, cmdType, cmdText, ConvertParams<SqlParameter>(parameters));
            return val;
        }

        /// <summary>
        /// 执行Sql
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override int ExecuteQuery(CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            var result = SqlHelper.ExecuteNonQuery(this._connection, cmdType, cmdText, ConvertParams<SqlParameter>(parameters));
            return result;
        }

        /// <summary>
        /// 执行Sql，返回是否成功执行
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override ResultInfo ExecuteTransactionQuery(string transactionName, CommandType cmdType, string cmdText, params IDataParameter[] parameters)
        {
            var transaction = this._connection.BeginTransaction(transactionName);
            try
            {
                SqlHelper.ExecuteNonQuery(transaction, cmdType, cmdText, ConvertParams<SqlParameter>(parameters));
                transaction.Commit();
                return ResultInfo.SetResult();
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return ResultInfo.SetResult(false, ex.ToString());
            }
        }

        public override int ExecuteNonQuery(int identityId, CommandType commandType, string commandText, params IDataParameter[] parameters)
        {
            return 0;
        }

        /// <summary>
        /// 检查表是否存在，若存在返回列集合
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public override bool CheckTable(string tableName, out DBColumn[] columns)
        {
            columns = null;
            string cmdText = string.Format("SELECT count(*) FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'U')", tableName);
            if (SqlHelper.ExecuteScalar(this._connection, CommandType.Text, cmdText).ToInt16() > 0)
            {
                var list = new List<DBColumn>();
                cmdText = string.Format("select c.name as ColumnName, t.name as ColumnType, c.scale, c.length,c.isnullable from syscolumns c join systypes t on c.xtype=t.xtype where t.name <> 'sysname' and c.id=object_id('{0}') order by colorder ASC", tableName);

                using (var reader = SqlHelper.ExecuteReader(this._connection, CommandType.Text, cmdText))
                {
                    while (reader.Read())
                    {
                        var column = new DBColumn();
                        column.Name = reader[0].ToNotNullString();
                        column.DbType = reader[1].ToNotNullString();
                        column.Scale = reader[2].ToInt32();
                        column.Length = reader[3].ToInt64();
                        column.Type = ConvertToObjectType(ConvertToDbType(column.DbType));
                        list.Add(column);
                    }
                }
                columns = list.ToArray();
                return true;
            }
            return false;
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columns"></param>
        public override void CreateTable(string tableName, DBColumn[] columns)
        {
            StringBuilder sbSql = new StringBuilder();
            try
            {
                sbSql.AppendFormat("Create Table {0}", FormatName(tableName));
                sbSql.AppendLine("(");
                List<string> keys;
                sbSql.AppendLine(MakeColumnString(columns, out keys));
                if (columns.Length > 0)
                {
                    if (keys.Count > 0)
                    {
                        sbSql.AppendLine(",");
                        sbSql.AppendFormat("constraint PK_{0} primary key({1})", tableName, FormatQueryColumn(",", keys));
                    }
                    sbSql.AppendLine();
                    sbSql.AppendLine(")");

                    SqlHelper.ExecuteNonQuery(this._connection, CommandType.Text, sbSql.ToString());
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Execute sql error:{0}", tableName), ex);
            }
        }

        public override void CreateColumn(string tableName, DBColumn[] columns)
        {
            if (columns == null || columns.Length <= 0)
                return;

            StringBuilder command = new StringBuilder();
            try
            {
                command.AppendFormat("Alter Table {0}", FormatName(tableName));
                command.AppendLine(" Add");
                List<string> keys;
                command.AppendLine(MakeColumnString(columns, out keys));
                command.Append(";");
                SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, command.ToString());

                command.Clear();
                List<DBColumn> keyColumns = new List<DBColumn>();
                int index = 0;
                foreach (var dbColumn in columns)
                {
                    if (!dbColumn.IsModify)
                    {
                        continue;
                    }
                    if (dbColumn.IsKey)
                    {
                        keyColumns.Add(dbColumn);
                        continue;
                    }
                    if (index > 0)
                    {
                        command.AppendLine("");
                    }
                    command.AppendFormat("Alter Table {0} ALTER COLUMN {1} {2}{3}{4};",
                                         FormatName(tableName),
                                         FormatName(dbColumn.Name),
                                         ConvertToDbType(dbColumn.Type, dbColumn.DbType, dbColumn.Length, dbColumn.Scale, dbColumn.IsKey),
                                         dbColumn.Isnullable ? "" : " not null",
                                         (dbColumn.IsIdentity ? " IDENTITY(1,1)" : ""));
                    index++;
                }
                if (keyColumns.Count > 0)
                {
                    List<string> keyArray = new List<string>(keyColumns.Count);
                    command.AppendFormat("ALTER TABLE {0} DROP CONSTRAINT PK_{1};", FormatName(tableName), tableName);
                    command.AppendLine();
                    foreach (var keyColumn in keyColumns)
                    {
                        keyArray.Add(FormatName(keyColumn.Name));
                        command.AppendFormat("Alter Table {0} ALTER COLUMN {1} {2} not null;",
                                             FormatName(tableName),
                                             FormatName(keyColumn.Name),
                                             ConvertToDbType(keyColumn.Type, keyColumn.DbType, keyColumn.Length, keyColumn.Scale, keyColumn.IsKey));
                        command.AppendLine();
                    }
                    command.AppendFormat("ALTER TABLE {0} ADD CONSTRAINT PK_{1} PRIMARY KEY({2});",
                        FormatName(tableName),
                        tableName,
                        FormatQueryColumn(",", keyArray));
                }
                SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.Text, command.ToString());
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Execute sql error:{0}", command), ex);
            }
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="dbType"></param>
        /// <param name="size"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public override IDataParameter CreateParameter(string paramName, int dbType, int size, object value)
        {
            var param = SqlParamHelper.MakeInParam(paramName, (SqlDbType)dbType, size, value);
            return param;
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="paramName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public override IDataParameter CreateParameter(string paramName, object value)
        {
            var param = SqlParamHelper.MakeInParam(paramName, value);
            return param;
        }

        /// <summary>
        /// 格式化Sql中的列名
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public override string FormatName(string name)
        {
            name = SqlParamHelper.FormatName(name);
            return name;
        }

        /// <summary>
        /// 格式化Select语句中的列名
        /// </summary>
        /// <param name="splitChat"></param>
        /// <param name="columns"></param>
        /// <returns></returns>
        public override string FormatQueryColumn(string splitChat, ICollection<string> columns)
        {
            return SqlParamHelper.FormatQueryColumn(splitChat, columns);
        }


        #region Common Methods

        private Type ConvertToObjectType(SqlDbType toEnum)
        {
            switch (toEnum)
            {
                case SqlDbType.BigInt:
                    return typeof(Int64);
                case SqlDbType.Binary:
                    return typeof(Byte[]);
                case SqlDbType.Bit:
                    return typeof(Boolean);
                case SqlDbType.Char:
                    return typeof(String);
                case SqlDbType.DateTime:
                    return typeof(DateTime);
                case SqlDbType.Decimal:
                    return typeof(Decimal);
                case SqlDbType.Float:
                    return typeof(Double);
                case SqlDbType.Image:
                    return typeof(Object);
                case SqlDbType.Int:
                    return typeof(Int32);
                case SqlDbType.Money:
                    return typeof(Decimal);
                case SqlDbType.NChar:
                    return typeof(String);
                case SqlDbType.NText:
                    return typeof(String);
                case SqlDbType.NVarChar:
                    return typeof(String);
                case SqlDbType.Real:
                    return typeof(Single);
                case SqlDbType.SmallDateTime:
                    return typeof(DateTime);
                case SqlDbType.SmallInt:
                    return typeof(Int16);
                case SqlDbType.SmallMoney:
                    return typeof(Decimal);
                case SqlDbType.Text:
                    return typeof(String);
                case SqlDbType.Timestamp:
                    return typeof(Object);
                case SqlDbType.TinyInt:
                    return typeof(Byte);
                case SqlDbType.Udt://自定义的数据类型
                    return typeof(Object);
                case SqlDbType.UniqueIdentifier:
                    return typeof(Guid);
                case SqlDbType.VarBinary:
                    return typeof(Object);
                case SqlDbType.VarChar:
                    return typeof(String);
                case SqlDbType.Variant:
                    return typeof(Object);
                case SqlDbType.Xml:
                    return typeof(Object);
                default:
                    throw new ArgumentOutOfRangeException("toEnum");
            }
        }

        /// <summary>
        /// 转换为Dbtype字符串
        /// </summary>
        /// <param name="type"></param>
        /// <param name="dbType"></param>
        /// <param name="length"></param>
        /// <param name="scale"></param>
        /// <param name="isKey"></param>
        /// <returns></returns>
        private string ConvertToDbType(Type type, string dbType, long length, int scale, bool isKey)
        {
            if (type.Equals(typeof(Int64)))
            {
                return "BigInt";
            }
            if (type.Equals(typeof(Boolean)))
            {
                return "Bit";
            }
            if (type.Equals(typeof(DateTime)))
            {
                return "DateTime";
            }
            if (type.Equals(typeof(Decimal)))
            {
                return "Decimal(18, " + (scale == 0 ? 4 : scale) + ")";
            }
            if (type.Equals(typeof(Double)))
            {
                return "Float";
            }
            if (type.IsEnum || type.Equals(typeof(Int32)))
            {
                return "Int";
            }
            if (type.Equals(typeof(Single)))
            {
                return "Real";
            }
            if (type.Equals(typeof(Int16)))
            {
                return "SmallInt";
            }
            if (type.Equals(typeof(Byte)))
            {
                return "TinyInt";
            }
            if (type.Equals(typeof(Byte[])))
            {
                return "varbinary(max)";
            }

            if (string.Equals(dbType, "uniqueidentifier", StringComparison.CurrentCultureIgnoreCase) ||
                type.Equals(typeof(Guid)))
            {
                return "UniqueIdentifier";
            }
            if (string.Equals(dbType, "varchar", StringComparison.CurrentCultureIgnoreCase) ||
                type.Equals(typeof(String)))
            {
                if (isKey && length == 0)
                {
                    return "VarChar(100)";
                }
                return length > 0
                    ? length >= 4000 ? "text" : "VarChar(" + length + ")"
                    : "VarChar(255)";
            }

            if (string.Equals(dbType, "text", StringComparison.CurrentCultureIgnoreCase))
            {
                return dbType;
            }

            return "sql_variant";
        }

        /// <summary>
        /// command的字符串
        /// </summary>
        /// <param name="command"></param>
        /// <param name="columns"></param>
        /// <param name="keys"></param>
        /// <returns></returns>
        private string MakeColumnString(DBColumn[] columns, out List<string> keys)
        {
            StringBuilder sbCommand = new StringBuilder();
            keys = new List<string>();
            int index = 0;
            foreach (var dbColumn in columns)
            {
                if (index > 0)
                {
                    sbCommand.AppendLine(",");
                }
                if (dbColumn.IsKey)
                {
                    keys.Add(FormatName(dbColumn.Name));
                }
                sbCommand.AppendFormat("    {0} {1}{2}{3}",
                                     FormatName(dbColumn.Name),
                                     ConvertToDbType(dbColumn.Type, dbColumn.DbType, dbColumn.Length, dbColumn.Scale, dbColumn.IsKey),
                                     (dbColumn.Isnullable ? "" : " not null"),
                                     (dbColumn.IsIdentity ? " IDENTITY(1,1)" : ""));
                index++;
            }

            return sbCommand.ToString();
        }

        #endregion
    }
}
